/** @namespace */
var agr = agr || {};


/** @namespace */
agr.Model = function() {
    'use strict';

    /**
     * WebSQL database name
     * @private
     * @constant
     * @type {String}
     * */
    var DB_NAME = 'agr-colony';

    /**
     * WebSQL database description
     * @private
     * @constant
     * @type {String}
     * */
    var DB_DESC = 'Colony Management';

    /**
     * WebSQL database requested size, in bytes
     * @private
     * @constant
     * @type {Number}
     * */
    var DB_SIZE = 5 * 1024 * 1024;

    /**
     * WebSQL database expected version
     * @private
     * @constant
     * @type {String}
     * */
    var DB_EXPECTED = '';

    /**
     * WebSQL database current version
     * @private
     * @constant
     * @type {String}
     * */
    var DB_CURRENT = '1.0.0';

    /**
     * cached database object
     * @private
     * @type {Database}
     * */
    var db_private;

    /**
     * initializes database
     * @param {function(Database)} ready
     * @param {function(SQLError)} error
     * @private */
    var getDB = function(ready, error) {
        if (db_private) {
            ready(db_private);
            return;
        }
        db_private = window.openDatabase(DB_NAME, DB_EXPECTED, DB_DESC, DB_SIZE);

        if (db_private.version === DB_EXPECTED) {
            db_private.changeVersion(DB_EXPECTED, DB_CURRENT,
                    function(t) {
                        var i;
                        for (i = 0; i < agr.sql.init.length; i++) {
                            t.executeSql(agr.sql.init[i]);
                        }
                    },
                    error,
                    function() {ready(db_private);}
                    );
        } else if (db_private.version === DB_CURRENT) {
            ready(db_private);
        } else {
            //TODO unknown database version
            var ex = {name: 'UnknownDatabase',
                message: 'unknown database version encountered'};
            throw ex;
        }
    };

    var tbl = {
        BreedingPair: function BreedingPair() {},
        Colony: function Colony() {},
        Genotype: function Genotype() {},
        Experiment: function Experiment() {},
        ExperimentGroup: function ExperimentGroup() {},
        Intervention: function Intervention() {},
        Litter: function Litter() {},
        Mouse: function Mouse() {},
        TeamMember: function TeamMember() {}
    };

    var db_get = function get(ID, ready, error, transaction) {

        if (!ID) return ready({});

        var select_sql,
            args = [];

        if (typeof ID === 'object' && ID.view) {
            select_sql = ' select * from ' + this.name + 'View' + ' where ID = ?';
            args.push(ID.ID); // sorry, future-me
        } else {
            select_sql = ' select * from ' + this.name + ' where ID = ?';
            args.push(ID);
        }


        if (!transaction) {
            getDB(function(db) {
                db.transaction(function(t) {
                    t.executeSql(select_sql, args, function(t,r) {
                        var row;
                        if (r.rows.length === 1) {
                            return ready(r.rows.item(0));
                        }
                        else{
                            error("Not Found");
                        }
                    }, error);
                },error);
            },error);
        } else { // FIXME this is really ugly
            transaction.executeSql(select_sql, args, function(t,r) {
                var row;
                if (r.rows.length === 1) {
                    return ready(r.rows.item(0));
                }
                else{
                    error("Not Found");
                }
            }, error);
        }
    }

    var db_list = function list(ParentID, ready, error) {
        var sql,
            that = this,
            args = [],
            pchain = [],
            p,
            count,
            view;

        if (ParentID && typeof ParentID === 'object') {

            if (ParentID.count) {
                count = true;
                delete ParentID.count;
            }

            if (ParentID.view) {
                view = true;
                delete ParentID.view;
            }

            p = this;
            pchain.unshift(p); //queue

            while (true) {
                p = p.parent;
                if (!p) break;
                pchain.unshift(p);
            }

            $.each(ParentID, function(k,v) {
                args.push(v);

                while (pchain.length > 0 && k !== pchain[0].name) {
                    pchain.shift();
                }

                return false; //assuming just one prop passed
            });

            if (pchain.length === 0) { //otherID
                $.each(ParentID, function(k,v) {
                    if (tbl[k]) {
                        sql = ' select ' +
                             ((count)
                              ? 'count(*) as count'
                              : '*') +
                            ' from ' + that.name +
                            ((view)
                             ? 'View'
                             : '') +
                            //where BreedingPairID = ?
                            ' where ' + tbl[k].name + 'ID = ? ';
                    }
                    return false;
                });
            } else { //parents

                // select Mouse.* from Colony
                sql = ' select ' +
                    ((count)
                    ? 'count(*) as count'
                    : this.name +
                    ((view)
                    ? 'View'
                    : '') +
                    '.*') +
                    ' from ' + 
                    pchain[0].name +
                    ((pchain[0] === this && view)
                     ? 'View'
                     : ''
                    );

                $.each(pchain, function(k,v) {
                    if (k === 0) return;
                    // join Litter on
                    
                    sql += ' join ' + v.name + 
                    ((this === v && view)
                     ? 'View'
                     : ''
                    ) +
                    ' on ' +
                    // Litter.ColonyID = Colony.ID
                    v.name +
                    ((this === v && view)
                     ? 'View'
                     : ''
                    ) +
                    '.' + v.parent.name + 'ID' +
                    ' = ' + v.parent.name + '.ID ';
                });

                // where Colony.ID = ?
                if (this.parent) {
                    sql += ' where ' + pchain[0].name + '.ID = ? ';
                }
            }
        } else {
            if (this.parent) {
                sql = ' select * from ' + this.name +
                    ' where ' + this.parent.name + 'ID' + ' = ? ';
            } else {
                sql = ' select * from ' + this.name;
            }

            args = (ParentID) ? [ParentID] : null;
        }

        var that = this;

        getDB(function(db) {
            var a = [];
            db.transaction(function(t) {
                t.executeSql(sql, args, function(t,r) {
                    var i;

                    if (count && r.rows.length === 1) {
                        return ready(r.rows.item(0));
                    } else {
                        // seriously?
                        for (i = 0; i < r.rows.length; i++) {
                            (function(row) {
                                $.each(that.siblings, function(k,v) {
                                    v.get(row[v.name + 'ID'],
                                        function(o) {
                                            row[v.name] = o;
                                        },error,t);
                                });
                                a.push(row);
                            }(r.rows.item(i)))
                        }
                    }
                });
            },error,function() {ready(a);});
        },error);
    }

    var db_remove = function remove(row, ready, error) {
        var delete_sql = ' delete from ' + this.name + ' where ID = ? ';

        getDB(function(db) {
            db.transaction(function(t) {
                t.executeSql(delete_sql, [row.ID], function(t,r) {
                    ready();
                });
            },error);
        },error);
    }

    var db_insert = function insert(row, ready, error) {
        var keys = [], vals = [];

        $.each(row, function(k,v) {
            if (typeof k === 'function') return;
            keys.push(k);
            vals.push(v);
        });

        var insert_sql = ' insert into ' + this.name +
            ' ( ' + keys.join(',') + ' ) ' +
            ' values ' +
            ' ( ' + keys.slice().map(function() {return '?';}).join(',') + ' ) ';

        getDB(function(db) {
            db.transaction(function(t) {
                t.executeSql(insert_sql, vals, function(t,r) {
                    if (r.rowsAffected === 1) {
                        ready(r.insertId);
                    } else {
                        ready();
                    }
                });
            },error);
        },error);
    }

    var db_update = function update(ParentID, ID, row, ready, error) {
        var keys = [],
            vals = [],
            sql;

        $.each(row, function(k,v) {
            if (typeof k === 'function') return;
            keys.push(k);
            vals.push(v);
        });

        if (ID) {//update
            vals.push(ID);

            sql = ' update ' + this.name + ' set ' +
                keys.join(' = ? , ') + ' = ? ' + ' where ID = ?';
        } else {//insert
            if (this.parent) {
                keys.push(this.parent.name + 'ID');
                vals.push(ParentID);
            }

            sql = ' insert into ' + this.name +
                ' ( ' + keys.join(',') + ' ) ' +
                ' values ' + ' ( ' +
                        keys.slice().map(function() {return '?';}).join(',') + ' ) ';
        }

        getDB(function(db) {
            db.transaction(function(t) {
                t.executeSql(sql, vals, function(t,r) {
                    if (ID) {
                        return ready(ID);
                    } else {
                        return ready(r.insertId);
                    }
                });
            },error);
        },error);
    }

    var sqlOrderedGroups =
        ' select ' +
        '     eg.ID ' +
        ' from ' +
        '     Experiment as e ' +
        ' join ' +
        '     ExperimentGroup as eg on ' +
        '         eg.ExperimentID = e.ID ' +
        ' where ' +
        '     e.Priority <> 0 ' +
        '     and ' +
        '     e.ColonyID = ? ' +
        ' order by ' +
        '     e.Priority asc ' +
        '     , ' +
        '     (eg.AgeDaysUpperLimit - eg.AgeDaysLowerLimit) asc ';

    var sqlClearInactive = 
        ' delete from ' +
        '     Intervention  ' +
        ' where ' +
        '     Active = 0 ' +
        '     and ' +
        '     ExperimentGroupID = ? ';

    var sqlGetOptimal =
        ' select distinct  ' +
        '     m.ID as MouseID  ' +
        '     ,eg.ID as ExperimentGroupID  ' +
        '     ,case   ' +
        '         when julianday(l.DateOfBirth) + eg.AgeDaysLowerLimit < julianday("now") then date("now")  ' +
        '         else date(julianday(l.DateOfBirth) + eg.AgeDaysLowerLimit)  ' +
        '      end as StartDate  ' +
        '     ,case   ' +
        '         when julianday(l.DateOfBirth) + eg.AgeDaysLowerLimit < julianday("now") then date(julianday("now") + e.DefaultDurationDays)  ' +
        '         else date(julianday(l.DateOfBirth) + eg.AgeDaysLowerLimit + e.DefaultDurationDays)  ' +
        '     end as EndDate  ' +
        ' from  ' +
        '     ExperimentGroup eg  ' +
        ' join  ' +
        '     Experiment e on  ' +
        '         e.ID = eg.ExperimentID  ' +
        ' join  ' +
        '     Experiment e_lower on  ' +
        '         e_lower.Priority >= e.Priority  ' +
        '         and  ' +
        '         e_lower.ColonyID = e.ColonyID  ' +
        ' join    ' +
        '     ExperimentGroup eg_lower on  ' +
        '         eg_lower.ExperimentID = e_lower.ID  ' +
        '         and  ' +
        '         ((eg_lower.AgeDaysUpperLimit - eg_lower.AgeDaysLowerLimit) >=  ' +
                '                     (eg.AgeDaysUpperLimit - eg.AgeDaysLowerLimit)  ' +
                '                 or e.Priority < e_lower.Priority)  ' +
        ' left join  ' +
        '     Intervention i on  ' +
        '         i.Active = 0  ' +
        '         and  ' +
        '         i.ExperimentGroupID = eg_lower.ID  ' +
        ' join ' +
        '     Litter l on ' +
        '         l.ColonyID = e.ColonyID ' +
        ' join  ' +
        '     Mouse m on  ' +
        '         m.LitterID = l.ID ' +
        '         and (i.MouseID = m.ID or m.ID not in (select MouseID from Intervention))  ' +
        '         and m.ID not in (select coalesce(MaleID,-1) from BreedingPair)  ' +
        '         and m.ID not in (select coalesce(FemaleID,-1) from BreedingPair)  ' +
        '         and (m.GenotypeID = eg.GenotypeID or eg.GenotypeID is null)  ' +
        '         and ((julianday("now") - julianday(l.DateOfBirth)) < eg.AgeDaysUpperLimit ' +
                '                     or eg.AgeDaysUpperLimit = 0 ' +
                '                     or eg.AgeDaysUpperLimit is null)  ' +
        '         and m.Gender = eg.Gender  ' +
        '         and m.Disabled = 0  ' +
        '         and m.Tag is not null  ' +
        '         and m.Tag <> ""  ' +
        ' where  ' +
        '     eg.ID = ?  ' +
        ' order by  ' +
        '     l.DateOfBirth asc  ' +
        ' limit  ' +
        '     (select MouseQuantity from ExperimentGroup where ID = ?) - (select count(*) from Intervention where Active > 0 and ExperimentGroupID = ?) ';

    var sqlDelIntByMouseID = 
        ' delete from ' +
        '     Intervention  ' +
        ' where ' +
        '     MouseID = ? ';

    var sqlInsertIntervention =
        ' insert into ' +
        '     Intervention ' +
        '     (ExperimentGroupID, MouseID, StartDate, EndDate) ' +
        ' values ' +
        '     (?,?,?,?) ';

    var schedColony = function reschedule(ColonyID, ready, error) {
        getDB(function(db) {
            db.transaction(function(t) {
                t.executeSql(sqlOrderedGroups,
                    [ColonyID],
                    function(t,r) {
                        var i,
                            eg_arr = [];
                        for (i = 0; i < r.rows.length; i++) {
                            eg_arr.push(r.rows.item(i));
                        }
                        schedPerGroup(t,eg_arr);
                    });
            },error,ready);
        },error);
    }

    var schedPerGroup = function(t, eg_arr) {
        var row = eg_arr.shift(); //queue
        if (!row) return;

        t.executeSql(
                sqlClearInactive,
                [row.ID],
                function(t) {
                    t.executeSql(
                        sqlGetOptimal,
                        [row.ID,row.ID,row.ID],
                        function(t,r) {
                            var i,
                                m_arr=[];
                            for (i = 0; i < r.rows.length; i++) {
                                m_arr.push(r.rows.item(i));
                            }
                            schedMouseToGroup(t, eg_arr, m_arr);
                        });
                    });
    }

    var schedMouseToGroup = function(t, eg_arr, m_arr) {
        var row = m_arr.pop(); //order irrelevant
        if (!row) return schedPerGroup(t,eg_arr);

        t.executeSql(
                sqlDelIntByMouseID,
                [row.MouseID],
                function(t) {
                    t.executeSql(sqlInsertIntervention,
                        [row.ExperimentGroupID,
                        row.MouseID,
                        row.StartDate,
                        row.EndDate],
                        function() {schedMouseToGroup(t, eg_arr, m_arr)}
                        );
                });
    }

    tbl.BreedingPair.parent = 
        tbl.Genotype.parent = 
        tbl.Experiment.parent =
        tbl.Litter.parent =
        tbl.TeamMember.parent =
        tbl.Colony;

    tbl.ExperimentGroup.parent = tbl.Experiment;

    tbl.Intervention.parent = tbl.ExperimentGroup;

    tbl.Mouse.parent = tbl.Litter;

    tbl.Mouse.siblings = [tbl.Genotype];

    tbl.Intervention.siblings = [tbl.Mouse];

    tbl.Litter.siblings = [tbl.BreedingPair];

    // need to special-case breedingpair MaleID, FemaleID at some point

    (function(O) {
        for (var i in O) {
            O[i].get = db_get;
            O[i].list = db_list;
            O[i].remove = db_remove;
            O[i].update = db_update;
            O[i].siblings = O[i].siblings || [];
        }
    }(tbl));

    tbl.Colony.reschedule = schedColony;

    tbl.__dump__ = function(ready, error) {
        var dump = [];
        getDB(function(db) {
            db.transaction(function (t) {
                t.executeSql('select tbl_name, sql from sqlite_master ' +
                    ' where type = "table" and tbl_name not like "#_#_%" escape "#"',
                    null,function(t,r) {
                        var i, tables = [];
                        for (i=0;i<r.rows.length;i++) {
                            tables.push(r.rows.item(i).tbl_name);
                            dump.push(r.rows.item(i).sql);
                        }
                        dumptable(t, dump, tables);
                });
            },error,
            function() {ready(dump);});
        },error);
    }

    var dumptable = function(t, dump_arr, table_arr) {
        var table = table_arr.pop();
        if (!table) return;

        t.executeSql(' select * from ' + table + ' limit 1 ', null,
                function(t,r) {
                    if (r.rows.length !== 1) {
                        return dumptable(t, dump_arr, table_arr);
                    }
                    var cols = [],
                        ins_str,
                        sel_str;

                    $.each(r.rows.item(0),function(k) {
                        cols.push(k);
                    });

                    ins_str = ' insert into ' + table + ' ( ' + cols.join(',') + ' ) values ( ';

                    (function() {
                        var quoted = cols.map(function(v,i,a) {
                            return 'quote('+v+')';
                        });

                        sel_str = ' select ' + quoted.join(',') + ' from ' + table;
                    }());

                    t.executeSql(sel_str, null, function(t,r) {
                        var i,
                            vals=[];
                        for (i = 0; i < r.rows.length; i++) {
                            vals.length = 0;
                            $.each(r.rows.item(i), function(_,v) {
                                vals.push(v);
                            });
                            dump_arr.push(ins_str + vals.join(',') + ')');
                        }

                        dumptable(t, dump_arr, table_arr);
                    });
                });
    }

    tbl.__reset__ = function(ready, error) {
        getDB(function(db) {
            db.transaction(function (t) {
                t.executeSql('select tbl_name from sqlite_master ' +
                    ' where type = "table" and tbl_name not like "#_#_%" escape "#"',null,
                    function (t,r) {
                        var i,
                            drop_sql,
                            $test = $.Deferred();
                        for (i=0; i < r.rows.length; i++) {
                            drop_sql = ' drop table ' + r.rows.item(i).tbl_name;
                            t.executeSql(drop_sql);
                        }
                        for (i = 0; i < agr.sql.init.length; i++) {
                            t.executeSql(agr.sql.init[i]);
                        }
                    });
            },error,ready);
        },error);
    }

    /** @scope agr.Model */
    return tbl;
}();
